﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class sqlScolarite{

        private static Scolarite ReaderToObject(SqlDataReader dr){
            Scolarite _scolarite = new Scolarite();
            _scolarite.Id = int.Parse(dr["Id"].ToString());//el badaltha tawwa entre les [] hadika el esm f table ok ok

            _scolarite.Nom = dr["Nom"].ToString();//nn 5ali les comm bech kif tansew ta9raw ya tofla , jit fel7a f nthafa tawwa lol ok
            _scolarite.Prenom = dr["Prenom"].ToString();
            _scolarite.Num_tel = dr["Num_tel"].ToString();

            _scolarite.Email = dr["Email"].ToString();

            if (!object.ReferenceEquals(dr["Cin"], DBNull.Value)){ 
                _scolarite.Cin = int.Parse(dr["Cin"].ToString()); 
            }

            if (!object.ReferenceEquals(dr["Id_authentification"], DBNull.Value)){
                _scolarite.authentificationScolarit = Authentification.GetAuthentificationById(int.Parse(dr["Id_authentification"].ToString()));
            }
            return _scolarite;
        }

        public static Scolarite GetScolariteById(int id){
            Scolarite scolarite = new Scolarite();
            string strSQL = @"select * from Scolarite where UserId=@Id";
            try{
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read()){
                        scolarite = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return scolarite;
        }
         
        public static int insertScolarite(Scolarite s)
        {
            int n = 0;            
            string strSQL = @"insert into Scolarite(Nom,Prenom,CIN,Num_tel,Email,Id_authentification) values(@nom,@prenom,@cin,@num_tel,@email,@id_authentification)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@cin", s.Cin);
                    cmd.Parameters.AddWithValue("@nom", s.Nom);
                    cmd.Parameters.AddWithValue("@prenom", s.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", s.Num_tel);
                    cmd.Parameters.AddWithValue(
                        "@email", s.Email);
                    cmd.Parameters.AddWithValue("@id_authentification", s.authentificationScolarit.Id);
                    
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();                    
                }
            }
            catch (Exception) { }
            return n;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateScolarite( Scolarite s)
        {
             int n = 0;

             string strSQl = @"UPDATE Scolarite SET (@nom IS NULL OR Nom=@nom) ,(@cin is null or Cin=@cin), (@prenom is null or Prenom=@prenom),"
            +" (@num_tel  is null or Num_tel=@num_tel), Email=@email, Id_authentification=@id_authentification where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    
                     SqlCommand cmd = new SqlCommand(strSQl, cnx);
                    cmd.Parameters.AddWithValue("@cin", s.Cin);
                    cmd.Parameters.AddWithValue("@nom", s.Nom);
                    cmd.Parameters.AddWithValue("@prenom", s.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", s.Num_tel);
                    cmd.Parameters.AddWithValue("@email", s.Email);     
                    cmd.Parameters.AddWithValue("@id_authentification", s.authentificationScolarit.Id);                   
                    n= cmd.ExecuteNonQuery();
                    
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }
       


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deletScolarite(int id, int idAuthentif){ 
            int n = 0;            
            string strSQL = @"delete from Scolarite where id=@id)";
            string strSQl2=@"UPDATE Authentification SET ACTIF='0' where id=@idAuth and ACTIF=1";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);//req pr delete
                     SqlCommand cmd2 = new SqlCommand(strSQl2, cnx);//req pr update authentif
                    cmd.Parameters.AddWithValue("@id", id);                   
                    cmd2.Parameters.AddWithValue("@idAuth", idAuthentif);
                    if(cmd.ExecuteNonQuery()>0)//si delete ok 
                      n=  cmd2.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }

    }
}
